package com.dems.dao;

import com.dems.domain.Repair;
import com.dems.domain.Student;
import com.dems.util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class RepairDao {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    //根据楼号查询所有维修未完成的订单
    public List<Repair> selectRepairBybuildingId(String buildingId) {
    	try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from repair where building_id=? and repair_state!='维修成功' ";
            ps = conn.prepareStatement(sql);
            ps.setString(1,buildingId);
            rs = ps.executeQuery();
            List<Repair> repairList = new ArrayList<>();
            while (rs.next()) {
                Repair repair = new Repair();
                repair.setRepairId(rs.getInt("repair_id"));
                repair.setRepairType(rs.getString("repair_type"));
                repair.setRepairDescription(rs.getString("repair_description"));
                repair.setRepairState(rs.getString("repair_state"));
                repair.setBuildingId(rs.getString("building_id"));
                repair.setStudentName(rs.getString("student_name"));
                repair.setStudentPhone(rs.getString("student_phone"));
                repair.setStudentDormitory(rs.getString("student_dormitory"));
                repair.setMpNum(rs.getString("mp_num"));
                repair.setMpName(rs.getString("mp_name"));
                repair.setMpPhone(rs.getString("mp_phone"));
                repairList.add(repair);
            }
            return repairList;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtils.close(conn,ps,rs);
        }
    }
    
  //根据楼号查询所有维修完成的订单
    public List<Repair> selectRepairBybuildingIdComplete(String buildingId) {
    	try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from repair where building_id=? and repair_state='维修成功' ";
            ps = conn.prepareStatement(sql);
            ps.setString(1,buildingId);
            rs = ps.executeQuery();
            List<Repair> repairList = new ArrayList<>();
            while (rs.next()) {
                Repair repair = new Repair();
                repair.setRepairId(rs.getInt("repair_id"));
                repair.setRepairType(rs.getString("repair_type"));
                repair.setRepairDescription(rs.getString("repair_description"));
                repair.setRepairState(rs.getString("repair_state"));
                repair.setBuildingId(rs.getString("building_id"));
                repair.setStudentName(rs.getString("student_name"));
                repair.setStudentPhone(rs.getString("student_phone"));
                repair.setStudentDormitory(rs.getString("student_dormitory"));
                repair.setMpNum(rs.getString("mp_num"));
                repair.setMpName(rs.getString("mp_name"));
                repair.setMpPhone(rs.getString("mp_phone"));
                repairList.add(repair);
            }
            return repairList;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtils.close(conn,ps,rs);
        }
    }
    
    //根据维修工人工号查询所有维修的订单
    public List<Repair> selectRepairBybuildingId(String buildingId,String repairType) {
    	try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from repair where building_id=? and repair_type=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,buildingId);
            ps.setString(2, repairType);
            rs = ps.executeQuery();
            List<Repair> repairList = new ArrayList<>();
            while (rs.next()) {
                Repair repair = new Repair();
                repair.setRepairId(rs.getInt("repair_id"));
                repair.setRepairType(rs.getString("repair_type"));
                repair.setRepairDescription(rs.getString("repair_description"));
                repair.setRepairState(rs.getString("repair_state"));
                repair.setBuildingId(rs.getString("building_id"));
                repair.setStudentName(rs.getString("student_name"));
                repair.setStudentPhone(rs.getString("student_phone"));
                repair.setStudentDormitory(rs.getString("student_dormitory"));
                repair.setMpNum(rs.getString("mp_num"));
                repair.setMpName(rs.getString("mp_name"));
                repair.setMpPhone(rs.getString("mp_phone"));
                repairList.add(repair);
            }
            return repairList;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtils.close(conn,ps,rs);
        }
    }
    
    //根据学生学号查询所有维修的订单
    public List<Repair> selectRepairBystudentNum(String studentNum) {
    	try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from repair where student_num=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,studentNum);
            rs = ps.executeQuery();
            List<Repair> repairList = new ArrayList<>();
            while (rs.next()) {
                Repair repair = new Repair();
                repair.setRepairId(rs.getInt("repair_id"));
                repair.setRepairType(rs.getString("repair_type"));
                repair.setRepairDescription(rs.getString("repair_description"));
                repair.setRepairState(rs.getString("repair_state"));
                repair.setBuildingId(rs.getString("building_id"));
                repair.setStudentName(rs.getString("student_name"));
                repair.setStudentPhone(rs.getString("student_phone"));
                repair.setStudentDormitory(rs.getString("student_dormitory"));
                repair.setMpNum(rs.getString("mp_num"));
                repair.setMpName(rs.getString("mp_name"));
                repair.setMpPhone(rs.getString("mp_phone"));
                repairList.add(repair);
            }
            return repairList;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtils.close(conn,ps,rs);
        }
    }

    //根据学生学号查询未维修完成的订单
    public List<Repair> selectReByNotRepair(String studentNum) {
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from repair where student_num=? and repair_state!='维修完成' ";
            ps = conn.prepareStatement(sql);
            ps.setString(1,studentNum);
            rs = ps.executeQuery();
            List<Repair> repairList = new ArrayList<>();
            while (rs.next()) {
                Repair repair = new Repair();
                repair.setRepairId(rs.getInt("repair_id"));
                repair.setRepairType(rs.getString("repair_type"));
                repair.setRepairDescription(rs.getString("repair_description"));
                repair.setRepairState(rs.getString("repair_state"));
                repair.setBuildingId(rs.getString("building_id"));
                repair.setStudentName(rs.getString("student_name"));
                repair.setStudentPhone(rs.getString("student_phone"));
                repair.setStudentDormitory(rs.getString("student_dormitory"));
                repair.setMpNum(rs.getString("mp_num"));
                repair.setMpName(rs.getString("mp_name"));
                repair.setMpPhone(rs.getString("mp_phone"));
                repairList.add(repair);
            }
            return repairList;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtils.close(conn,ps,rs);
        }
    }

    //根据学生学号查询已经维修完成的订单
    public List<Repair> selectRepairByaIsRepair(String studentNum) {
    	try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from repair where student_num=? and repair_state='维修完成' ";
            ps = conn.prepareStatement(sql);
            ps.setString(1,studentNum);
            rs = ps.executeQuery();
            List<Repair> repairList = new ArrayList<>();
            while (rs.next()) {
                Repair repair = new Repair();
                repair.setRepairId(rs.getInt("repair_id"));
                repair.setRepairType(rs.getString("repair_type"));
                repair.setRepairDescription(rs.getString("repair_description"));
                repair.setRepairState(rs.getString("repair_state"));
                repair.setBuildingId(rs.getString("building_id"));
                repair.setStudentName(rs.getString("student_name"));
                repair.setStudentPhone(rs.getString("student_phone"));
                repair.setStudentDormitory(rs.getString("student_dormitory"));
                repair.setMpNum(rs.getString("mp_num"));
                repair.setMpName(rs.getString("mp_name"));
                repair.setMpPhone(rs.getString("mp_phone"));
                repairList.add(repair);
            }
            return repairList;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtils.close(conn,ps,rs);
        }
    }

    //新增维修单
    public void saveRepair(Repair repair) {
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement("insert into repair values(null,?,?,?,?,?,?,?,?,?,?,?)");
            ps.setString(1, repair.getRepairType());
            ps.setString(2, repair.getRepairDescription());
            ps.setString(3,repair.getRepairState());
            ps.setString(4,repair.getBuildingId());
            ps.setString(5,repair.getStudentNum());
            ps.setString(6,repair.getStudentName());
            ps.setString(7,repair.getStudentPhone());
            ps.setString(8,repair.getStudentDormitory());
            ps.setString(9,"");
            ps.setString(10,"");
            ps.setString(11,"");
            ps.executeUpdate();
        } catch (Exception e) {
        	e.printStackTrace();
			throw new RuntimeException(e);
        }finally{
            JDBCUtils.close(conn, ps, null);
        }
    }
    
    //确认维修
  	public void repairResult(int repairId,String studentNum,String repairState) {
  		int i = 0;
  		try {
  			conn = JDBCUtils.getConnection();
  			ps = conn.prepareStatement("update repair set repair_state=? where student_num=? and repair_id=?");
  			ps.setString(1, repairState);
  			ps.setString(2, studentNum);
  			ps.setInt(3, repairId);
  			i = ps.executeUpdate();
  		} catch (Exception e) {
  			e.printStackTrace();
  		}finally{
  			JDBCUtils.close(conn, ps, null);
  		}
  	}
  	
  	 //确认维修
  	public void repairResult(int repairId,String repairState) {
  		int i = 0;
  		try {
  			conn = JDBCUtils.getConnection();
  			ps = conn.prepareStatement("update repair set repair_state=? where repair_id=?");
  			ps.setString(1, repairState);
  			ps.setInt(2, repairId);
  			i = ps.executeUpdate();
  		} catch (Exception e) {
  			e.printStackTrace();
  		}finally{
  			JDBCUtils.close(conn, ps, null);
  		}
  	}
  	
    //去维修
  	public void toRepair(int repairId,String mpName,String mpPhone,String repairState) {
  		int i = 0;
  		try {
  			conn = JDBCUtils.getConnection();
  			ps = conn.prepareStatement("update repair set repair_state=?,mp_name=?,mp_phone=? where repair_id=?");
  			ps.setString(1, repairState);
  			ps.setString(2, mpName);
  			ps.setString(3, mpPhone);
  			ps.setInt(4, repairId);
  			i = ps.executeUpdate();
  		} catch (Exception e) {
  			e.printStackTrace();
  		}finally{
  			JDBCUtils.close(conn, ps, null);
  		}
  	}
}
